<?php
/**
 * Created by JetBrains PhpStorm.
 * User: andrei
 * Date: 7/31/12
 * Time: 1:30 PM
 * To change this template use File | Settings | File Templates.
 */

class Db_Projects extends Common {
    protected static $tableName = TAB_PROJECTS;
    protected static $dbFields = array(
        'id',
        'pr_country_id',
        'pr_name',
        'pr_identifier',
		'pr_theme',
        'pr_needs_login',
        'pr_domain_test',
        'pr_domain_live',
        'pr_resources_test',
        'pr_resources_live',
        'pr_email',
        'pr_skin',
        'deleted'
    );

    /**
     * @var Db_Projects
     */
    public $objectArray;

    public $id;
    public $pr_country_id;
    public $pr_name;
    public $pr_identifier;
	public $pr_theme;
    public $pr_needs_login;
    public $pr_domain_test;
    public $pr_domain_live;
    public $pr_resources_test;
    public $pr_resources_live;
    public $pr_email;
    public $pr_skin;
    public $deleted;

    function __construct($value=NULL, $fieldName=NULL) {
        parent::__construct($value, $fieldName);
    }

	/**
	 * @static
	 *
	 */
	public static function setCurrentProject(){
		if (!self::$PROJECT) {
			$sql = "SELECT `pr`.*, `c`.`c_name`, `c`.`c_identifier` FROM ?f AS `pr`
					INNER JOIN ?f AS `c` ON `c`.`id` = `pr`.`pr_country_id`
					WHERE `pr`.`pr_identifier` = ? AND `pr`.`deleted` = 0 AND `c`.`c_identifier` = ? AND `c`.`deleted` = 0;";
			self::$PROJECT = self::$DB->selectRow($sql, self::$tableName, TAB_COUNTRIES, PROJECT, COUNTRY);
			if (!defined('CLI')) {
				$skin = Db_Cms_Skins::getCurrentSkin();
				self::$PROJECT['skin'] = $skin;
				self::$PROJECT['general_skin_files'] = Db_Cms_GeneralSkinFiles::getCurrentGeneralSkinFiles();
			}
		}
	}

	/**
	 * @static
	 * @return mixed
	 */
	public static function getCurrentProjectId(){
		self::setCurrentProject();
		return self::$PROJECT['id'];
	}

	/**
	 * @static
	 * @return mixed
	 */
	public static function getAllProjects(){
        $sql = "SELECT `pr`.*, `pr`.`id` AS `pr_id`, `c`.*, `cp`.`cp_project_id` AS `is_projects_categories` FROM ?f AS `pr`
                LEFT JOIN ?f AS `c` ON `pr`.`pr_country_id` = `c`.`id`
                INNER JOIN ?f AS `cp` ON `cp`.`cp_project_id` = `pr`.`id`
                WHERE `pr`.`deleted` = 0  GROUP BY `cp`.`cp_project_id`";
        return self::$DB->select($sql, self::$tableName, TAB_COUNTRIES, TAB_CATEGORY_PROJECTS);
    }

	/**
	 * @static
	 * @param $projectId
	 * @return mixed
	 */
	public static function getProjectById($projectId){
        $sql = "SELECT `pr`.*, `pr`.`id` AS `pr_id`, `c`.*, `c`.`id` AS `c_id` FROM ?f AS `pr`
                LEFT JOIN ?f AS `c` ON `pr`.`pr_country_id` = `c`.`id` WHERE `pr`.`id` = ?d AND `pr`.`deleted` = 0 ";
        return self::$DB->selectRow($sql, self::$tableName, TAB_COUNTRIES, $projectId);
    }

	/**
	 * @static
	 * @return mixed
	 */
	public static function getDistinctProjects(){
        $sql = "SELECT DISTINCT `pr_identifier` FROM ?f WHERE `deleted` = 0;";
        return self::$DB->select($sql, self::$tableName);
    }

	/**
	 * @static
	 * @param $projectId
	 * @param $languages
	 */
	public static function saveProjectLanguages($projectId, $languages){
		$sql = "DELETE FROM ?f WHERE `prl_project_id`=?i;";
		self::$DB->q($sql, TAB_PROJECT_LANGUAGES, $projectId);
		foreach($languages as $key => $value){
			$sql = "INSERT INTO ?f (`prl_project_id`, `prl_language_id`, `prl_is_default`) VALUES (?i, ?i, ?i);";
            $default = ($key == 0 ? 1 : 0 );
            self::$DB->q($sql, TAB_PROJECT_LANGUAGES, $projectId, $value, $default);
		}
 	}

	/**
	 * @static
	 * @param $projectId
	 * @return mixed|null
	 */
	public static function getProjectLanguages($projectId){
		if(!$projectId){
			return null;
		}else {
			$sql = "SELECT `pl`.`prl_language_id` FROM ?f AS `pl` WHERE `pl`.`prl_project_id` = ?i ORDER BY `prl_is_default` DESC ;";
			return self::$DB->selectCol($sql, TAB_PROJECT_LANGUAGES, $projectId);
		}
	}

	public static function getProjectsIdentifierByMenus($menus = array()) {
		if (!$menus || count($menus) == 0) {
			return array();
		}
		$listIds = implode(',',array_map(array('MySQL', 'escapeValue'), $menus));
		$sql = "SELECT pr.pr_identifier FROM ?f pr
				INNER JOIN ?f m ON pr.pr_identifier = m.m_project_identifier AND m.deleted = 0
				WHERE m.id IN ({$listIds}) AND pr.deleted = 0 GROUP BY pr.pr_identifier
		";
		$result = self::$DB->q($sql, self::$tableName, TAB_MENUS);
		$dataReturn = array();
		while ($data = $result->fetch_object()) {
			$dataReturn[] = $data->pr_identifier;
		}
		return $dataReturn;

	}

	/**
	 * This function gets all Projects,  fields (Id, Name) and all categories and menus which belong each projects
	 * @static
	 * @return array
	 */
	public static function getProjects() {
		$sql = "SELECT DISTINCT `c`. `c_identifier`, `pr`.`id` as `projectId`, `pr`.`pr_name`, `pr`.`pr_identifier`, `cp`.`cp_project_id`, `cp`.`cp_category_id`, `cat`.`id`, `m`.`id` as `menuId`, GROUP_CONCAT(`m`.`id`) as `menus`, GROUP_CONCAT(`cat`.`id`) as `categories` FROM ?f pr
		LEFT JOIN ?f cp ON `pr`.`deleted` = 0 AND `cp`.`cp_project_id` = `pr`.`id`
		LEFT JOIN ?f m ON `m`.`deleted` = 0 AND `m`.`m_project_identifier` = `pr`.`pr_identifier`
		LEFT JOIN ?f cat ON `cat`.`deleted` = 0 AND `cat`.`id` = `cp`.`cp_category_id`
		LEFT JOIN ?f c ON `c`.`id` =  `pr`.`pr_country_id`
		GROUP BY `pr`.`id`";
		$projects = self::$DB->q(true, $sql, TAB_PROJECTS, TAB_CATEGORY_PROJECTS, TAB_MENUS, TAB_CATEGORIES, TAB_COUNTRIES);
		$dataReturn = array();

		while($data = $projects->fetch_assoc()) {
			$categories = array();
			$menus = array();
			if (isset($data['categories'])) {
				$categoryId = array_unique(explode(",", $data['categories']));
				foreach ($categoryId as $category) {
					$categories[] = "cat".$category;
				}
			}
			if (isset($data['menus'])){
				$menuId = array_unique(explode(",", $data['menus']));
				foreach ($menuId as $menu) {
					$menus[] = "menu".$menu;
				}
			}
			$dataReturn[] = array(
				"id" => "proj".$data['projectId'],
				"name" => t(T_PROJECT_TITLE, $data['pr_name']). " (" . $data['c_identifier'] . ")",
				"categories" => $categories,
				"menus" => $menus
			);

		}
		return $dataReturn;
	}
}
